﻿/*********************************************************************************************************************
VanguardDocumentation, View Level0Entities

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'V'
			and o.name = 'Level0Entities'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create view VanguardDocumentation.Level0Entities
		as select ''View Level0Entities is not implemented yet!'' as Error
	';
	execute (@SqlCommand);
end;
GO

alter view VanguardDocumentation.Level0Entities
	/*****************************************************************************************************************
	Lists all entities on level 0 for extended properties.
	
	The column SearchOrder is intended for when the user only gives a name (no type). The order is:
	SCHEMA, USER, ASSEMBLY, TRIGGER (DDL trigger), FILEGROUP, PARTITION FUNCTION, PARTITION SCHEME, 
	MESSAGE TYPE, CONTRACT, SERVICE, REMOTE SERVICE BINDING, ROUTE, EVENT NOTIFICATION
	*****************************************************************************************************************/
as
select 1 as SearchOrder, 'SCHEMA' as Level0Type, s.name collate database_default as Level0Name
from sys.schemas as s

-- Despite the name it is also possible to add extended properties to roles. So no condition to filter only users.
union all
select 2 as SearchOrder, 'USER' as Level0Type, u.name collate database_default as Level0Name
from sys.database_principals as u

union all
select 3 as SearchOrder, 'ASSEMBLY' as Level0Type, a.name collate database_default as Level0Name
from sys.assemblies as a

union all
select 4 as SearchOrder, 'TRIGGER' as Level0Type, t.name collate database_default as Level0Name
from sys.triggers as t
where t.parent_class = 0 -- only DDL triggers

union all
select 5 as SearchOrder, 'FILEGROUP' as Level0Type, d.name collate database_default as Level0Name
from sys.data_spaces as d
where d.[type] = 'FG'

union all
select 6 as SearchOrder, 'PARTITION FUNCTION' as Level0Type, p.name collate database_default as Level0Name
from sys.partition_functions as p

union all
select 7 as SearchOrder, 'PARTITION SCHEME' as Level0Type, p.name collate database_default as Level0Name
from sys.partition_schemes as p

-- I haven't used service broker myself, so the SearchOrder below is just guesswork.
-- Please correct if you have a better idea of what entities you would most likely add documenation to.
union all
select 8 as SearchOrder, 'MESSAGE TYPE' as Level0Type, m.name collate database_default as Level0Name
from sys.service_message_types as m

union all
select 9 as SearchOrder, 'CONTRACT' as Level0Type, c.name collate database_default as Level0Name
from sys.service_contracts as c

union all
select 10 as SearchOrder, 'SERVICE' as Level0Type, s.name collate database_default as Level0Name
from sys.services  as s

union all
select 11 as SearchOrder, 'REMOTE SERVICE BINDING' as Level0Type, b.name collate database_default as Level0Name
from sys.remote_service_bindings as b

union all
select 12 as SearchOrder, 'ROUTE' as Level0Type, r.name collate database_default as Level0Name
from sys.routes as r

union all
select 13 as SearchOrder, 'EVENT NOTIFICATION' as Level0Type, e.name collate database_default as Level0Name
from sys.event_notifications as e;
GO




